package dao;

import pojo.Student;
import sun.security.util.Password;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author konley
 * @date 2020-06-09 20:24
 */
public class StudentDao {
    //根据sid查
    public Student getById(int sid){
        Student stu = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection con = DBUtils.getConnection();
        try {
            ps = con.prepareStatement("select * from Student where sid=?");
            ps.setString(1,sid+"");
            rs = ps.executeQuery();
            if(rs.next()){
                stu = new Student();
                stu.setSid(rs.getInt("sid"));
                stu.setName(rs.getString("name"));
                stu.setPassword(rs.getString("password"));
                stu.setSex(rs.getString("sex"));
                stu.setDept(rs.getString("dept"));
                stu.setTel(rs.getString("tel"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(rs,ps,con);
        }
        return stu;
    }
    //根据名字模糊查询
    public List<Student> getByName(String name){
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        List<Student> list = new ArrayList<Student>();
        try {
            ps = con.prepareStatement("select * from student where name like ?");
            ps.setString(1,"%"+name+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                Student stu = new Student();
                stu.setSid(rs.getInt("sid"));
                stu.setName(rs.getString("name"));
                stu.setPassword(rs.getString("password"));
                stu.setSex(rs.getString("sex"));
                stu.setDept(rs.getString("dept"));
                stu.setTel(rs.getString("tel"));
                list.add(stu);
            }
            if(list.size()==0){
                list = null;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(rs,ps,con);
        }
        return list;
    }

    //修改学生信息
    public boolean studentUpdate(Student stu){
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        try {
            ps = con.prepareStatement("update student set name=?,password=?,sex=?,dept=?,tel=? where sid=?;");
            ps.setString(1,stu.getName());
            ps.setString(2,stu.getPassword());
            ps.setString(3,stu.getSex());
            ps.setString(4,stu.getDept());
            ps.setString(5,stu.getTel());
            ps.setInt(6,stu.getSid());
            int num = ps.executeUpdate();
            if(num > 0){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return false;
    }

    //添加学生信息
    public boolean studentAdd(int sid, String name, String password,String sex,String dept,String tel){
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        try{
            ps = con.prepareStatement("insert into student value(?,?,?,?,?,?)");
            ps.setInt(1,sid);
            ps.setString(2,name);
            ps.setString(3,password);
            ps.setString(4,sex);
            ps.setString(5,dept);
            ps.setString(6,tel);
            int num = ps.   executeUpdate();
            if(num > 0){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return false;
    }

    //删除学生
    public boolean deleteStudent(int sid){
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        try{
            ps = con.prepareStatement("delete from student where sid=?");
            ps.setInt(1,sid);
            int num = ps.executeUpdate();
            if(num > 0){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return false;
    }
}
